/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package sg.edu.nus.iss.phoenix.schedule.dao.impl;

import sg.edu.nus.iss.phoenix.core.dao.DBConstants;
import sg.edu.nus.iss.phoenix.schedule.dao.ScheduleDAO;
import sg.edu.nus.iss.phoenix.schedule.entity.ProgramSlot;

import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;

/**
 * Schedule DAO - This class contains the functions to perform CRUD operations on the database pertaining to schedule
 *
 * @author tham
 */
public class ScheduleDAOImpl implements ScheduleDAO {
    Connection connection;

    /**
     * GetProgramSlots-method. This method retrieves the program slots from database based on the year and week
     *
     * @param year The year
     * @param week week in the year
     */

    public ArrayList<ProgramSlot> GetProgramSlots(int year, int week) {
        openConnection();
        //String sql = "SELECT * FROM `program-slot`";// WHERE 'year'=? AND 'week'=?;";
        // String sql = "SELECT * FROM `program-slot` WHERE `year`=? AND `week`=?";
        //SELECT WEEK(date) FROM phoenix.`program-slot` where YEAR(date)=9999 and week=2;
        String sql = "SELECT * FROM `program-slot` WHERE YEAR(`dateOfProgram`)=? AND WEEK(`dateOfProgram`)=?";
        PreparedStatement stmt = null;
        ArrayList<ProgramSlot> searchResults = new ArrayList<ProgramSlot>();
        try {
            stmt = connection.prepareStatement(sql);

            stmt.setInt(1, year);
            stmt.setInt(2, week);
            searchResults = listQuery(stmt);
            for (ProgramSlot s : searchResults) {
                s.setWeek(week);
            }
        } catch (Exception e) {

        } finally {
            closeConnection();
        }
        return searchResults;

    }

    /**
     * GetAllProgramSlots-method. This method retrieves all the program slots from the database
     */
    public ArrayList<ProgramSlot> GetAllProgramSlots() {
        openConnection();
        //String sql = "SELECT * FROM `program-slot`";// WHERE 'year'=? AND 'week'=?;";
        // String sql = "SELECT * FROM `program-slot` WHERE `year`=? AND `week`=?";
        //SELECT WEEK(date) FROM phoenix.`program-slot` where YEAR(date)=9999 and week=2;
        String sql = "SELECT * FROM `program-slot`";
        PreparedStatement stmt = null;
        ArrayList<ProgramSlot> searchResults = new ArrayList<ProgramSlot>();
        try {
            stmt = connection.prepareStatement(sql);

            searchResults = listQuery(stmt);

        } catch (Exception e) {

        } finally {
            closeConnection();
        }
        return searchResults;
    }

    /**
     * CreateProgramSlot-method. This method creates a program slot
     *
     * @param slotInsert The program slot to insert
     */
    public boolean CreateProgramSlot(ProgramSlot slotInsert) {
        openConnection();
        //String sql = "SELECT * FROM `program-slot`";// WHERE 'year'=? AND 'week'=?;";
        // String sql = "SELECT * FROM `program-slot` WHERE `year`=? AND `week`=?";
        //SELECT WEEK(date) FROM phoenix.`program-slot` where YEAR(date)=9999 and week=2;
        String sql = "INSERT INTO `program-slot` (`duration`, `dateOfProgram`,`program-name`, `presenter`, `producer`) VALUES (0, ?, ?, ?, ?)";
        PreparedStatement stmt = null;
        ArrayList<ProgramSlot> searchResults = new ArrayList<ProgramSlot>();
        try {
            stmt = connection.prepareStatement(sql);
            long timeMilli = slotInsert.getDate().getTimeInMillis();

            Timestamp ts = new Timestamp(timeMilli);
            //stmt.setInt(1, 0);
            //stmt.setTime(1, slotInsert.getDispDate());
            stmt.setTimestamp(1, ts);
            stmt.setString(2, slotInsert.getName());
            stmt.setString(3, slotInsert.getPresenter());
            stmt.setString(4, slotInsert.getProducer());
            stmt.executeUpdate();
        } catch (Exception e) {
            return false;
        } finally {
            closeConnection();
        }

        return true;
    }

    /**
     * ModifyProgramSlot-method. This method modifies a program slot in the database
     *
     * @param slot the program slot to modify
     */
    public boolean ModifyProgramSlot(ProgramSlot slot) {
        String sql = "UPDATE `program-slot` SET `program-name` = ?, `presenter` = ?, `producer` = ? WHERE (`dateOfProgram`=? ) ";
        PreparedStatement stmt = null;
        openConnection();

        try {
            stmt = connection.prepareStatement(sql);
            long timeMilli = slot.getDate().getTimeInMillis();

            Timestamp ts = new Timestamp(timeMilli);
            //stmt.setInt(1, 0);
            //stmt.setTime(1, slotInsert.getDispDate());

            stmt.setString(1, slot.getName());
            stmt.setString(2, slot.getPresenter());
            stmt.setString(3, slot.getProducer());
            stmt.setTimestamp(4, ts);
            stmt.executeUpdate();

        } catch (Exception e) {
            return false;
        } finally {
            closeConnection();
        }
        return true;
    }

    /**
     * DeleteProgramSlot-method. This method deletes a program slot from the database
     *
     * @param slot The program slot to delete
     */
    public boolean DeleteProgramSlot(ProgramSlot slot) {

        openConnection();
        //String sql = "SELECT * FROM `program-slot`";// WHERE 'year'=? AND 'week'=?;";
        // String sql = "SELECT * FROM `program-slot` WHERE `year`=? AND `week`=?";
        //SELECT WEEK(date) FROM phoenix.`program-slot` where YEAR(date)=9999 and week=2;
        String sql = "DELETE FROM phoenix.`program-slot` WHERE (YEAR(`dateOfProgram`)=? and MONTH(`dateOfProgram`)=? and `program-name`=?)";
        PreparedStatement stmt = null;
        // ArrayList<ProgramSlot> searchResults=new ArrayList<ProgramSlot>() ;
        try {
            stmt = connection.prepareStatement(sql);

            //stmt.setInt(1, 0);

            stmt.setInt(1, slot.getDate().get(Calendar.YEAR));
            stmt.setInt(2, slot.getDate().get(Calendar.MONTH) + 1);
            stmt.setString(3, slot.getName());
            stmt.executeUpdate();
        } catch (Exception e) {
            return false;
        } finally {
            closeConnection();
        }

        return true;
    }

    /**
     * listQuery-method. This method runs an SQL prepared statement and returns a list of program slot
     *
     * @param stmt an sql prepared statement
     */
    protected ArrayList<ProgramSlot> listQuery(PreparedStatement stmt) throws SQLException {

        ArrayList<ProgramSlot> searchResults = new ArrayList<ProgramSlot>();
        ResultSet result = null;
        openConnection();
        try {
            result = stmt.executeQuery();

            while (result.next()) {
                ProgramSlot temp = new ProgramSlot();

                temp.setName(result.getString("program-name"));
                temp.setPresenter(result.getString("presenter"));
                temp.setProducer(result.getString("producer"));
                Date dateGet = result.getDate("dateOfProgram");

                //Timestamp ts = result.getTimestamp("dateOfProgram");
                Calendar cal = Calendar.getInstance();
                cal.setTime(dateGet);
//temp.setDispDate(dateGet.toString());
                //int day = dateGet.getDay();
                int year = cal.get(Calendar.YEAR);
                int month = cal.get(Calendar.MONTH);
                int day = cal.get(Calendar.DAY_OF_MONTH);
                int week = cal.get(Calendar.WEEK_OF_YEAR);
                int hour = cal.get(Calendar.HOUR_OF_DAY);
                temp.setWeek(week - 1);
                GregorianCalendar calG = new GregorianCalendar();

                //calG.set(year, month, day, hour, 0, 0);
                //calG.setTime(dateGet);

                Time time = result.getTime("dateOfProgram");
                Timestamp ts = result.getTimestamp("dateOfProgram");
                hour = ts.getHours();
                calG.set(year, month, day, hour, 0, 0);
                //long timeMilli = time.getTime();
                //calG.setTimeInMillis(timeMilli);
                temp.setDate(calG);
                temp.setTime(time);
                searchResults.add(temp);
            }

        } finally {
            if (result != null)
                result.close();
            if (stmt != null)
                stmt.close();
            closeConnection();
        }

        return searchResults;
    }

    /**
     * openConnection-method. This method opens a connection in the database
     */
    private void openConnection() {
        try {
            Class.forName(DBConstants.COM_MYSQL_JDBC_DRIVER);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        try {
            this.connection = DriverManager.getConnection(DBConstants.dbUrl,
                    DBConstants.dbUserName, DBConstants.dbPassword);
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    /**
     * closeConnection-method. This method closes a connection in the database
     */
    private void closeConnection() {
        try {
            this.connection.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}
